const express = require('express');

const router = express.Router();
//导入数据库操作模块
const db = require('../db/index');

//创建借阅记录
router.post('/create', (req, res) => {
    console.log(req.body);
    //获取用户id
    const { user_id, goods_id, day, status, create_time } = req.body;

    //插入数据之前先检查是否存在该用户和书籍的借阅记录
    db.query('SELECT * FROM records WHERE user_id =? AND goods_id =? and status = 0', [user_id, goods_id], (err, result) => {
        if (err) {
            res.json({
                code: 500,
                message: '数据库查询失败'
            })
        } else {
            if (result.length > 0) {
                res.json({
                    code: 300,
                    message: '购物车已存在'
                })
            } else {
                const sql = `insert into records(user_id, goods_id, day, status, create_time) values(?,?,?,?,?)`;
                //插入数据
                db.query(sql, [user_id, goods_id, day, status, create_time], (err, result) => {
                    if (err) {
                        res.json({
                            code: 500,
                            message: '服务器错误[1]'
                        })
                    } else {
                        res.json({
                            code: 200,
                            message: '操作成功'
                        })
                    }
                })

            }
        }
    })
})

//获取用户借阅清单
router.get('/list', (req, res) => {
    let { user_id } = req.query

    //sql查询 查询记录 并且 通过记录的goods_id获取goods表信息
    let sql = `select r.*,b.title,b.pic,b.count,b.price from records r left join goods b on r.goods_id=b.id where r.user_id=? and r.status=0 order by r.create_time desc`

    db.query(sql, [user_id], (err, result) => {
        if (err) {
            console.log(err);
            res.send({
                code: 500,
                message: '服务器错误'
            })
        } else {

            res.send({
                code: 200,
                message: '获取成功',
                books: result
            })
        }
    })
})

//获取用户借阅记录
router.get('/record', (req, res) => {
    let { user_id } = req.query

    //获取所有status为2的记录，将当前时间比create_time多3天的记录设置为3
    db.query(`UPDATE records SET status = 3 WHERE status = 2 AND create_time <= DATE_SUB(NOW(), INTERVAL 3 DAY);`, (err, result) => {
        if (err) {
            console.log(err);
        }
    })

    //sql查询 查询借阅记录 并且 通过借阅记录的goods_id获取book表图书信息
    let sql = `select r.*,b.title,b.pic,b.count,b.price from records r left join goods b on r.goods_id=b.id where r.user_id=? and r.status !=0 order by r.create_time desc`

    db.query(sql, [user_id], (err, result) => {
        if (err) {
            console.log(err);
            res.send({
                code: 500,
                message: '服务器错误'
            })
        } else {
            res.send({
                code: 200,
                message: '获取成功',
                books: result
            })
        }
    })

})

// 删除
router.post('/delete', (req, res) => {
    const {
        id
    } = req.body
    const sql = 'delete from records where id =? and status = 0'
    db.query(sql, [id], (err, result) => {
        if (err) {
            res.send({
                code: 500,
                message: '服务器错误'
            })
        } else {
            res.send({
                code: 200,
                message: '删除成功'
            })
        }
    })
})


// 确定购买
router.post('/confirm', (req, res) => {

    const { list } = req.body;

    if (!list || list.length == 0) {
        res.send({
            code: 500,
            message: '请选择要购买的书籍'
        })
    }

    //通过list的id获取用户id，再通过用户id获取用户信息，判断其手机号和地址是否存在，若不存在提示用户信息不完整
    db.query('SELECT * FROM user WHERE id =?', [list[0].user_id], (err, result) => {
        if (err) {
            console.log(err);
            res.send({
                code: 500,
                message: '服务器错误[1]'
            })
        } else {
            if (result.length == 0) {
                res.send({
                    code: 500,
                    message: '用户信息不存在'
                })
            } else {
                if (result[0].phone == '' || result[0].address == '') {
                    res.send({
                        code: 500,
                        message: '收货信息不完整，请填写完整'
                    })
                } else {
                    let sql = '';
                    //循环遍历list生成sql
                    for (let i = 0; i < list.length; i++) {
                        sql += `update records set status = 1,day = ${list[i].day} where id = ${list[i].id};`
                    }

                    db.query(sql, (err, result) => {
                        if (err) {
                            res.send({
                                code: 500,
                                message: '服务器错误'
                            })
                        } else {
                            res.send({
                                code: 200,
                                message: '下单成功'
                            })
                        }
                    })
                }
            }
        }
    })
})

//修改状态
router.post('/returnBook', (req, res) => {
    let { id } = req.body;
    let sql = `update records set status = 3, create_time = now() where id = ${id}`;
    db.query(sql, (err, result) => {
        if (err) {
            console.log(err);
            res.send({
                code: 500,
                message: '数据库错误'
            })
        } else {
            res.send({
                code: 200,
                message: '确认收货成功'
            })
        }
    })
})

//获取所有记录 分页
router.get('/all', (req, res) => {
    let start = req.query.start;
    let limit = req.query.limit;


    //获取所有status为2的记录，将当前时间比create_time多3天的记录设置为3
    db.query(`UPDATE records SET status = 3 WHERE status = 2 AND create_time <= DATE_SUB(NOW(), INTERVAL 3 DAY);`, (err, result) => {
        if (err) {
            console.log(err);
        }
    })


    // sql 查询记录 并且 通过记录的goods_id获取goods表信息 
    const sql =
        `
        SELECT r.*, b.title, b.pic, b.count, b.price, u.username, u.nickname, u.phone, u.address
        FROM records r
        LEFT JOIN goods b ON r.goods_id = b.id
        LEFT JOIN user u ON r.user_id = u.id
        WHERE r.status != 0
        ORDER BY r.create_time DESC
        LIMIT ${start}, ${limit};
    `

    db.query(sql, async (err, orders) => {
        if (err) {
            console.log(err);
            res.send({
                code: 500,
                message: '获取失败'
            })
        } else {
            //获取搜索总数
            const totalSql =
                `
                SELECT r.*, b.title, b.pic, b.count, b.price, u.username, u.nickname, u.phone, u.address
                FROM records r
                LEFT JOIN goods b ON r.goods_id = b.id
                LEFT JOIN user u ON r.user_id = u.id
                WHERE r.status != 0
                ORDER BY r.create_time DESC
            `;

            db.query(totalSql, (err, result) => {
                if (err) {
                    res.json({
                        code: 500,
                        message: '服务器错误[2]'
                    })
                } else {
                    if (result.length > 0) {
                        res.json({
                            code: 200,
                            message: '获取成功',
                            total: result[0].total,
                            orders
                        })
                    } else {
                        res.json({
                            code: 200,
                            message: '获取成功',
                            total: 0,
                            orders
                        })
                    }
                }
            })
        }
    })
})

//搜索
router.get('/search', (req, res) => {
    let { keyword, limit, start } = req.query;

    // sql 查询记录 并且 通过记录的goods_id获取goods表信息 
    const sql =
        `
    SELECT r.*, b.title, b.pic, b.count, b.price, u.username, u.nickname, u.phone, u.address
    FROM records r
    LEFT JOIN goods b ON r.goods_id = b.id
    LEFT JOIN user u ON r.user_id = u.id
    WHERE r.status != 0 and (b.title LIKE '%${keyword}%' or r.id = '${keyword}' or r.goods_id = '${keyword}' or r.user_id = '${keyword}')
    ORDER BY r.create_time DESC
    LIMIT ${+start}, ${+limit};
    `

    db.query(sql, (err, orders) => {
        if (err) {
            console.log(err);
            res.json({
                code: 500,
                message: '服务器错误[1]'
            })
        } else {
            //获取搜索总数
            const totalSql =
                `
            SELECT r.*, b.title, b.pic, b.count, b.price, u.username, u.nickname, u.phone, u.address
            FROM records r
            LEFT JOIN goods b ON r.goods_id = b.id
            LEFT JOIN user u ON r.user_id = u.id
            WHERE r.status != 0 and (b.title LIKE '%${keyword}%' or r.id = '${keyword}' or r.goods_id = '${keyword}' or r.user_id = '${keyword}')
            ORDER BY r.create_time DESC
            `;

            db.query(totalSql, (err, result) => {
                if (err) {
                    res.json({
                        code: 500,
                        message: '服务器错误[2]'
                    })
                } else {
                    if (result.length > 0) {
                        res.json({
                            code: 200,
                            message: '获取成功',
                            total: result[0].total,
                            orders
                        })
                    } else {
                        res.json({
                            code: 200,
                            message: '获取成功',
                            total: 0,
                            orders
                        })
                    }
                }
            })
        }
    })
})

// 批量删除
router.post('/del', (req, res) => {
    const { ids } = req.body;
    const sql = `DELETE FROM records WHERE id in (${ids.toString()})`;
    db.query(sql, (err, result) => {
        if (err) {
            console.log(err);
            res.send({
                code: 500,
                message: '服务器错误'
            })
        } else {
            res.send({
                code: 200,
                message: '删除成功'
            })
        }
    })
})

//修改状态
router.post('/updateStatus', (req, res) => {
    let { id, status } = req.body;
    let sql = `update records set status = ${status} where id = ${id}`;
    db.query(sql, (err, result) => {
        if (err) {
            console.log(err);
            res.send({
                code: 500,
                message: '操作失败'
            })
        } else {
            res.send({
                code: 200,
                message: '操作成功'
            })
        }
    })
})

//通过状态获取
router.get('/byStatus', (req, res) => {
    let { status, limit, start } = req.query;

    // sql 查询记录 并且 通过记录的goods_id获取goods表信息 
    const sql =
        `
    SELECT r.*, b.title, b.pic, b.count, b.price, u.username, u.nickname, u.phone, u.address
    FROM records r
    LEFT JOIN goods b ON r.goods_id = b.id
    LEFT JOIN user u ON r.user_id = u.id
    WHERE r.status = '${status}'
    ORDER BY r.create_time DESC
    LIMIT ${+start}, ${+limit};
    `

    db.query(sql, (err, orders) => {
        if (err) {
            console.log(err);
            res.json({
                code: 500,
                message: '服务器错误[1]'
            })
        } else {
            //获取搜索总数
            const totalSql =
                `
                SELECT r.*, b.title, b.pic, b.count, b.price, u.username, u.nickname, u.phone, u.address
                FROM records r
                LEFT JOIN goods b ON r.goods_id = b.id
                LEFT JOIN user u ON r.user_id = u.id
                WHERE r.status = '${status}'
                ORDER BY r.create_time DESC
            `;

            db.query(totalSql, (err, result) => {
                if (err) {
                    res.json({
                        code: 500,
                        message: '服务器错误[2]'
                    })
                } else {
                    if (result.length > 0) {
                        res.json({
                            code: 200,
                            message: '获取成功',
                            total: result[0].total,
                            orders
                        })
                    } else {
                        res.json({
                            code: 200,
                            message: '获取成功',
                            total: 0,
                            orders
                        })
                    }
                }
            })
        }
    })
})

//获取各状态的数量，全部返回
router.get('/getCount', (req, res) => {

    let sql = ''
    if (req.query.status) {
        let status = req.query.status;
        sql = `select count(*) as total from records where status = '${status}'`;
    } else {
        sql = `select count(*) as total from records`;
    }

    db.query(sql, (err, result) => {
        if (err) throw err;
        res.json({
            code: 200,
            message: '获取成功',
            total: result[0].total
        })
    })
})

//获取数据库中前一周，各天的订单量
router.get('/lastWeekOrders', (req, res) => {
    let sql = `select date_format(create_time,'%m-%d') as date, count(*) as total from records where status != 0 and create_time >= DATE_SUB(NOW(),INTERVAL 7 DAY) group by date ORDER BY   
    date ASC`;
    db.query(sql, (err, result) => {
        if (err) res.cc(err);
        res.json({
            code: 200,
            message: '获取成功',
            data: result
        })
    })
})

//获取数据库中前一周，各天的订单金额
router.get('/lastWeekOrdersMoney', (req, res) => {
    let sql = `SELECT   
    date_format(create_time,'%m-%d') AS date,  
    SUM(g.price) AS price  
FROM   
    records r  
JOIN   
    goods g ON r.goods_id = g.id  
WHERE   
    r.status = 3   
    AND r.create_time >= CURDATE() - INTERVAL 7 DAY  
GROUP BY   
date  
ORDER BY   
date;`;
    db.query(sql, (err, result) => {
        if (err) res.cc(err);
        res.json({
            code: 200,
            message: '获取成功',
            data: result
        })
    })
})


module.exports = router;